#!/bin/bash
#============= 全局变量 ==============
# 数据库IP
IP="127.0.0.1"
# 数据库端口
PORT="3306"
# 用户名
USER="root"
# 密码
PASSWORD="123456"
MASTER_DOCKER_NAME=mysql5719_master
# 数据库名称
DBNAME=gms_0407
# 备份目录
BACK_PATH=/root/centos7_common_software/mysql
# 忽略表
IGNORE_TABLE="--ignore-table=$DBNAME.sm_dev_operate_log --ignore-table=$DBNAME.sm_calibration_log --ignore-table=$DBNAME.sm_work_plan_log --ignore-table=$DBNAME.sys_job_log --ignore-table=$DBNAME.sys_oper_log"
#============= 工具方法 ==============
# (1)只备份表结构 
function backup_structonly (){
    if [ ! -d "$BACK_PATH/'structonly'" ]; then
        echo "创建备份目录：$BACK_PATH/'structonly'"
        mkdir -p $BACK_PATH/'structonly'
    fi  
    file_path=$BACK_PATH/"structonly"/$DBNAME"_databasestructonly_"$(date "+%Y%m%d_%H%M%S").sql
    echo "开始数据库备份$DBNAME表结构，备份路径：$file_path"
    docker exec $MASTER_DOCKER_NAME mysqldump -h $IP -P $PORT -u $USER -p$PASSWORD  --opt --no-data $DBNAME > $file_path
    #mysqldump -h $IP -P $PORT -u $USER -p$PASSWORD  --opt --no-data $DBNAME > $file_path
}

# (2)备份所有：结构+数据+函数+存储过程
function backup_structdata (){
    if [ ! -d "$BACK_PATH/'structdata'" ]; then
        echo "创建备份目录：$BACK_PATH/'structdata'"
        mkdir -p $BACK_PATH/'structdata'
    fi  
    file_path=$BACK_PATH/"structdata"/$DBNAME"_databasestructdata_"$(date "+%Y%m%d_%H%M%S").sql
    echo "开始数据库备份$DBNAME所有表数据，备份路径：$file_path"
    docker exec $MASTER_DOCKER_NAME mysqldump -h $IP -P $PORT -u $USER -p$PASSWORD $IGNORE_TABLE --opt -R $DBNAME > $BACK_PATH//"structdata"/$DBNAME"_databasestructdata_"$(date "+%Y%m%d_%H%m%S").sql
    # mysqldump -h $IP -P $PORT -u $USER -p$PASSWORD $IGNORE_TABLE --opt -R $DBNAME > $BACK_PATH//"structdata"/$DBNAME"_databasestructdata_"$(date "+%Y%m%d_%H%m%S").sql
}
# (3)恢复数据备份
function recover_data(){
    #mysql -u $USER -p$PASSWORD $DBNAME < $1
    docker exec -i $MASTER_DOCKER_NAME mysql -u $USER -p$PASSWORD $DBNAME < $1
}

# (4)删除大于30天的文件
function remove30date(){
    find $BACK_PATH/ -mtime +30 -name "*.sql"
    # find $BACK_PATH/ -mtime +30 -name "*.sql" -exec rm -rf {} \;
}
#============= 调用选项 ==============
case "$1" in
    "data")
        echo "备份$DBNAME"
        backup_structdata
        remove30date
    ;;
    "struct")
        echo "备份$DBNAME表结构"
        backup_structonly
        remove30date
    ;;
    "recover")
        echo "恢复$DBNAME"
        recover_data $2
    ;;
    "remove30date")
        echo "删除大于30天的数据库备份文件：$DBNAME"
        remove30date
    ;;
    *)
        echo "脚本调用错误，请传对应参数"
        echo "用法：mysql_backup.sh 参数"
        echo "三种参数：data | struct | recover '数据库文件.sql路径'"
    ;;
esac
#============= 安装crontab及命令 ==============
# yum -y install crontabs
# systemctl enable crond
# systemctl start crond
# 保存生效
# crontab /etc/crontab
#查看任务
# crontab -l

#============= 定时配置/etc/crontab ==============
# Example of job definition:
# .---------------- minute (0 - 59)
# |  .------------- hour (0 - 23)
# |  |  .---------- day of month (1 - 31)
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# |  |  |  |  |
# *  *  *  *  * user-name  command to be executed

#0 0 * * * /home/mysql_backup/mysql_backup.sh data >/dev/null 2>&1



